﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataTier
{
    [Serializable]
    public class Klage_billede
    {
        private Klage_billede()
        {

        }

        #region Properties
        private int klagenr;

        public int KlageNr
        {
            get { return klagenr; }
            set { klagenr = value; }
        }
        private int pictureId;

        public int PictureId
        {
            get { return pictureId; }
            set { pictureId = value; }
        }
        private string picture_link;

        public string PictureLinkStr
        {
            get { return picture_link; }
            set { picture_link = value; }
        }

        #endregion

        public static List<Klage_billede> Load(string connStr, int klageNr)
        {
            var toReturn = new List<Klage_billede>();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlStr = "select * from klage_billede where klagenr = " + klageNr;
                    var sqlCommand = new SqlCommand(sqlStr, conn);
                    var sqlReader = sqlCommand.ExecuteReader();


                    while (sqlReader.Read())
                    {
                        toReturn.Add(new Klage_billede
                        {
                            klagenr = int.Parse(sqlReader.GetInt16(0).ToString()),
                            pictureId = int.Parse(sqlReader.GetInt16(1).ToString()),
                            picture_link = sqlReader.GetString(2)
                        });
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }
            return toReturn;
        }

        private static int GetPictureId(string connStr, int klageNr, string picture_link)
        {
            var toReturn = -1;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlStr = "select pictureid from klage_billede where klagenr = " + klageNr + " and picture_link = '" + picture_link + "'";
                    var sqlCommand = new SqlCommand(sqlStr, conn);
                    var sqlReader = sqlCommand.ExecuteReader();


                    if (sqlReader.Read())
                    {
                        toReturn = int.Parse(sqlReader.GetInt16(0).ToString());
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }
            return toReturn;
        }

        public static Klage_billede Insert(string connStr, int klageNr, string picture_link)
        {
            var toReturn = new Klage_billede
            {
                klagenr = klageNr,
                pictureId = -1,
                picture_link = picture_link
            };
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlCommand = new SqlCommand("insert into klage_billede (klagenr, picture_link) values (@klagenr, @picture_link)", conn);

                    sqlCommand.Parameters.Add("@klagenr", SqlDbType.SmallInt);
                    sqlCommand.Parameters.Add("@picture_link", SqlDbType.VarChar, 250);

                    sqlCommand.Parameters["@klagenr"].Value = toReturn.klagenr;
                    sqlCommand.Parameters["@picture_link"].Value = toReturn.picture_link;

                    var sqlReader = sqlCommand.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }
            toReturn.pictureId = GetPictureId(connStr, toReturn.klagenr, toReturn.picture_link);
            return toReturn;

        }

        public void Save(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlCommand = new SqlCommand("update klage_billede set picture_link = @picture_link,  klagenr = @klagenr where pictureid = @pictureid", conn);

                    sqlCommand.Parameters.Add("@picture_link", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@klagenr", SqlDbType.SmallInt);
                    sqlCommand.Parameters.Add("@pictureid", SqlDbType.SmallInt);

                    /*
 * klagenr	smallint
pictureid	smallint
picture_link	varchar(250)
*/
                    sqlCommand.Parameters["@picture_link"].Value = this.picture_link;
                    sqlCommand.Parameters["@klagenr"].Value = this.klagenr;
                    sqlCommand.Parameters["@pictureid"].Value = this.pictureId;

                    var sqlReader = sqlCommand.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }

        }

        public void Delete(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlCommand = new SqlCommand("delete from klage_billede where klagenr = @klagenr and pictureid = @pictureid", conn);

                    sqlCommand.Parameters.Add("@klagenr", SqlDbType.SmallInt);
                    sqlCommand.Parameters.Add("@pictureid", SqlDbType.SmallInt);

                    sqlCommand.Parameters["@klagenr"].Value = this.klagenr;
                    sqlCommand.Parameters["@pictureid"].Value = this.pictureId;

                    var sqlReader = sqlCommand.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

    }
}
